﻿using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using Microsoft.Data.Sqlite;

namespace PPSMS_DAL;

public class DBHelper
{
    private static SqliteConnectionStringBuilder _connectionStringBuilder;

    public static readonly Func<DataTable, DataTable> ExecuteScalarDatatable = dt =>
    {
        return dt;
    };

    public static readonly Func<DataTable, int> ExecuteScalarToInt = dt =>
    {
        if (dt.Rows.Count == 0)
        {
            return 0;
        }
        return (int)Convert.ToInt64(dt.Rows[0][0]);
    };

    public static readonly Func<DataTable, string> ExecuteScalarToStringResultHandler = dt =>
    {
        if (dt.Rows.Count == 0)
        {
            return null;
        }
        return $"{dt.Rows[0][0]}";
    };

    public static readonly Func<DataTable, bool> ExecuteScalarToBoolResultHandler = dt =>
    {
        if (dt.Rows.Count == 0)
        {
            return false;
        }
        return Convert.ToBoolean(dt.Rows[0][0]);
    };

    public static readonly Func<DataTable, long> ExecuteScalarToLongResultHandler = dt =>
    {
        if (dt.Rows.Count == 0)
        {
            return 0;
        }
        return Convert.ToInt64(dt.Rows[0][0]);
    };

    /// <summary>
    /// 数据库初始化
    /// </summary>
    /// <param name="databaseName"></param>
    /// <param name="tableDdl"></param>
    public static void Init(string databaseName, Dictionary<string, string> tableDdl = null)
    {
        _connectionStringBuilder = new SqliteConnectionStringBuilder()
        {
            DataSource = databaseName
        };
        // 检查databaseName父目录是否存在
        var directory = Path.GetDirectoryName(databaseName); // 得到数据库文件的父目录
        if (!string.IsNullOrWhiteSpace(directory) && !Directory.Exists(directory))
        {
            // 如果不存在则创建
            Directory.CreateDirectory(directory);
        }

        if (tableDdl is { Count: > 0 })
        {
            foreach (var kv in tableDdl)
            {
                ExecuteNonQuery(kv.Value);
            }
        }
    }
    /// <summary>
    /// 获取数据库连接
    /// </summary>
    /// <returns></returns>
    public static SqliteConnection GetConnection()
    {
        var conn = new SqliteConnection(_connectionStringBuilder.ToString());
        conn.Open();
        return conn;
    }

    /// <summary>
    /// 处理 SQL 查询字符串，将其中的参数占位符（?）替换为具名参数（如@p0, @p1等），并生成对应的 SqliteParameter 数组
    /// </summary>
    /// <param name="sql">包含占位符？的SQL查询字符串</param>
    /// <param name="parameters">存储生成的参数的SqliteParameter 数组</param>
    /// <param name="args">实际参数数组</param>
    /// <returns>去掉参数占位符后的SQL查询字符串</returns>
    private static string SqlHandler(string sql, out SqliteParameter[] parameters, params object[] args)
    {
        // 使用正则表达式,将sql中的?替换为@p0,@p1,@p2...
        var regex = new Regex(@"\?"); // 创建一个正则表达式对象
        var matches = regex.Matches(sql); // 匹配sql中的?得到一个匹配集合
        // 循环匹配集合,将?替换为@p0,@p1,@p2...
        parameters = new SqliteParameter[args.Length];

        for (int i = 0, j = 0; i < matches.Count; i++, j++)
        {
            sql = regex.Replace(sql, $"@p{i}", 1);
            parameters[i] = new SqliteParameter($"@p{i}", args[j]);

        }
        return sql;
    }

    /// <summary>
    /// 创建SqliteCommand对象
    /// </summary>
    /// <param name="connection">SQLite 数据库连接对象</param>
    /// <param name="sql">要执行的 SQL 语句</param>
    /// <param name="parameters">存储SqliteParameter 对象的数组</param>
    /// <returns>SqliteCommand 对象</returns>
    private static SqliteCommand CreateCommand(SqliteConnection connection, string sql, params SqliteParameter[] parameters)
    {
        using var cmd = connection.CreateCommand();
        cmd.CommandText = sql;
        cmd.Parameters.AddRange(parameters);
        return cmd;
    }

    /// <summary>
    /// 执行增删改语句（DML语句）
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static long ExecuteNonQuery(string sql)
    {
        return ExecuteNonQuery(sql, new object[0]);
    }

    /// <summary>
    /// 执行增删改语句（DML语句）
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="args"></param>
    /// <returns></returns>
    public static long ExecuteNonQuery(string sql, params object[] args)
    {
        sql = SqlHandler(sql, out var parameters, args);
        return ExecuteNonQuery(sql, parameters);
    }


    /// <summary>
    /// 执行DML语句
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="parameters">存储SqliteParameter 对象的数组</param>
    /// <returns>受影响的行数</returns>
    public static long ExecuteNonQuery(string sql, params SqliteParameter[] parameters)
    {
        using var conn = GetConnection();
        using var cmd = CreateCommand(conn, sql, parameters);
        return cmd.ExecuteNonQuery();
    }

    /// <summary>
    /// 执行一个 SQL 查询，并返回一个包含查询结果的列表
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">ＤＱＬ语句</param>
    /// <param name="resultHandler"></param>
    /// <param name="args"></param>
    /// <returns></returns>
    public static List<T> ExecuteQuery<T>(string sql, Func<DataRow, T> resultHandler, params object[] args)
    {
        sql = SqlHandler(sql, out var parameters, args);
        return ExecuteQuery(sql, resultHandler, parameters);
    }

    /// <summary>
    /// 执行一个 SQL 查询，并返回一个包含查询结果的列表
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">ＤＱＬ语句</param>
    /// <param name="resultHandler">接受一个 DataRow 类型的参数，并返回一个 T 类型的结果</param>
    /// <param name="parameters"></param>
    /// <returns>泛型列表</returns>
    public static List<T> ExecuteQuery<T>(string sql, Func<DataRow, T> resultHandler, params SqliteParameter[] parameters)
    {
        using var conn = GetConnection();
        using var cmd = CreateCommand(conn, sql, parameters);
        using var reader = cmd.ExecuteReader();
        var list = new List<T>();
        var dt = new DataTable();
        dt.Load(reader);
        foreach (DataRow row in dt.Rows)
        {
            list.Add(resultHandler(row));
        }
        return list;
    }

    /// <summary>
    /// 接受一个 SQL 查询和参数（SqliteParameter[]）,返回DataTable
    /// </summary>
    /// <typeparam name="T">任意类型</typeparam>
    /// <param name="sql">ＤＱＬ语句</param>
    /// <param name="resultHandler">Func委托，用于处理DataTable</param>
    /// <param name="parameters">可变参数，用于填充SQL语句中的参数</param>
    /// <returns>一个DataTable</returns>
    public static T ExecuteQuery<T>(string sql, Func<DataTable, T> resultHandler, params SqliteParameter[] parameters)
    {
        using var conn = GetConnection();
        using var cmd = CreateCommand(conn, sql, parameters);
        using var reader = cmd.ExecuteReader();
        var dt = new DataTable();
        dt.Load(reader);
        return resultHandler(dt);
    }

    /// <summary>
    /// 接受一个 SQL 查询和参数（object[]）,返回DataTable
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">ＤＱＬ语句</param>
    /// <param name="resultHandler"></param>
    /// <param name="args"></param>
    /// <returns></returns>
    public static T ExecuteQuery<T>(string sql, Func<DataTable, T> resultHandler, params object[] args)
    {
        sql = SqlHandler(sql, out var parameters, args);
        return ExecuteQuery(sql, resultHandler, parameters);
    }

    public static T ExecuteScalarList<T>(string sql, Func<DataTable, T> resultHandler, params SqliteParameter[] parameters)
    {
        return ExecuteQuery(sql, resultHandler, parameters);
    }
}